﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Jacaranda.Data
{
    class DataPortal
    {
        public static string ConnectionString { get; set; }

        public static DatabaseType DatabaseType { get; set; }


        private static DataAccess Access
        {
            get { return DataAccess.Create(); }
        }

        public static Transaction BeginTransaction()
        {
            DbConnection conn = Access.CreateDbConnection();
            conn.ConnectionString = ConnectionString;
            conn.Open();

            return new Transaction() { Connection = conn, DbTransaction = conn.BeginTransaction() };
        }

        public static void Execute(string sql)
        {
            DbConnection conn = Access.CreateDbConnection();
            conn.ConnectionString = ConnectionString;
            conn.Open();

            DbCommand command = Access.CreateDbCommand();
            
            command.Connection = conn;
            command.CommandText = sql;

            command.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
        }

        public static void Execute(string sql, Transaction transaction)
        {
            if (transaction == null)
            {
                Execute(sql);
                return;
            }

            DbCommand command = Access.CreateDbCommand();

            command.Connection = transaction.Connection;
            command.Transaction = transaction.DbTransaction;
            command.CommandText = sql;
 
            command.ExecuteNonQuery();
        }

        public static void Execute(DbCommand command)
        {
            DbConnection conn = Access.CreateDbConnection();
            conn.ConnectionString = ConnectionString;
            conn.Open();

            command.Connection = conn;

            command.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
        }

        public static void Execute(DbCommand command, Transaction transaction)
        {
            command.Connection = transaction.Connection;
            command.Transaction = transaction.DbTransaction;

            command.ExecuteNonQuery();
        }

        public static DataTable Select(string sql)
        {
            DbCommand selectCommand = Access.CreateDbCommand();
            selectCommand.CommandText = sql;

            DataTable dt = Select(selectCommand);
            selectCommand.Dispose();

            return dt;
        }

        public static DataTable Select(string sql, Transaction transaction)
        {
            DbCommand selectCommand = Access.CreateDbCommand();
            selectCommand.CommandText = sql;

            DataTable dt = Select(selectCommand, transaction);
            selectCommand.Dispose();

            return dt;
        }

        public static DataTable Select(DbCommand command)
        {
            DbConnection conn = Access.CreateDbConnection();
            conn.ConnectionString = ConnectionString;

            command.Connection = conn;


            DataSet dataSet = new DataSet();

            DbDataAdapter adapter = Access.CreateDbDataAdapter();
            adapter.SelectCommand = command;

            adapter.Fill(dataSet);

            conn.Close();
            conn.Dispose();

            return dataSet.Tables[0];
        }

        public static object SelectScalar(string sql)
        {
            DbConnection conn = Access.CreateDbConnection();
            conn.ConnectionString = ConnectionString;

            DbCommand command = Access.CreateDbCommand();
            
            command.Connection = conn;
            command.CommandText = sql;

            conn.Open();
            object scalar = command.ExecuteScalar();

            conn.Close();
            conn.Dispose();

            command.Dispose();

            return DBNull.Value.Equals(scalar) ? null : scalar;
        }

        public static object SelectScalar(DbCommand command)
        {
            DbConnection conn = Access.CreateDbConnection();
            conn.ConnectionString = ConnectionString;

            command.Connection = conn;
            conn.Open();
            object scalar = command.ExecuteScalar();

            conn.Close();
            conn.Dispose();

            command.Dispose();

            return DBNull.Value.Equals(scalar) ? null : scalar;
        }

        public static object SelectScalar(DbCommand command, Transaction transaction)
        {
            command.Connection = transaction.Connection;
            command.Transaction = transaction.DbTransaction;
            
            object scalar = command.ExecuteScalar();
            command.Dispose();

            return DBNull.Value.Equals(scalar) ? null : scalar;
        }

        public static DbConnection CreateConnection()
        {
            DbConnection conn = Access.CreateDbConnection();
            conn.ConnectionString = ConnectionString;

            return conn;
        }

        public static DataTable Select(DbCommand command, Transaction transaction)
        {
            command.Connection = transaction.Connection;
            command.Transaction = transaction.DbTransaction;


            DataSet dataSet = new DataSet();

            DbDataAdapter adapter = Access.CreateDbDataAdapter();
            adapter.SelectCommand = command;

            adapter.Fill(dataSet);

            return dataSet.Tables[0];
        }

        private static DbCommand CloneBuilderCommand(System.Data.Common.DbCommand from)
        {
            DbCommand to = Access.CreateDbCommand();

            to.CommandText = from.CommandText;
            to.Connection = from.Connection;
            to.Transaction = from.Transaction;

            //Copying parameters
            if (from.Parameters.Count == 0) return to;//No parameters to clone? go away!
            System.Data.Common.DbParameter[] parametersArray = new System.Data.Common.DbParameter[from.Parameters.Count];
            from.Parameters.CopyTo(parametersArray, 0);
            from.Parameters.Clear();//Removing association before link to the repository one
            to.Parameters.AddRange(parametersArray);

            return to;
        }

        public static void Update(string tableName, DataTable table, Transaction transaction)
        {
            DbCommand selectCommand = Access.CreateDbCommand();

            selectCommand.Connection = transaction.Connection;
            selectCommand.Transaction = transaction.DbTransaction;
            selectCommand.CommandText = string.Format("SELECT * FROM {0}", tableName);


            DbDataAdapter adapter = Access.CreateDbDataAdapter();
            adapter.SelectCommand = selectCommand;

            DbCommandBuilder builder = Access.CreateDbCommandBuilder();
            builder.DataAdapter = adapter;

            adapter.InsertCommand = CloneBuilderCommand(builder.GetInsertCommand());
            adapter.InsertCommand.CommandText = adapter.InsertCommand.CommandText.Replace("VALUES", "OUTPUT INSERTED.ID VALUES");
            adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

            adapter.UpdateCommand = builder.GetUpdateCommand();

            adapter.Update(table.DataSet);

            adapter.Dispose();
            selectCommand.Dispose();
        }
    }
}
